﻿namespace Huayou.Common
{
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;

    public class DBAccess : IDisposable
    {
        private SqlConnection connection;
        private string connectionString;

        public DBAccess()
        {
            this.connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            this.connection = new SqlConnection(this.connectionString);
        }

        public DBAccess(SqlConnection newConnection)
        {
            this.connectionString = newConnection.ConnectionString;
            this.connection = newConnection;
        }

        public DBAccess(string newConnectionString)
        {
            this.connectionString = newConnectionString;
            this.connection = new SqlConnection(this.connectionString);
        }

        public SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        public SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, this.connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        public void Close()
        {
            if (this.connection != null)
            {
                this.connection.Close();
            }
        }

        public void Dispose()
        {
            if (this.connection != null)
            {
                this.connection.Dispose();
                this.connection = null;
            }
        }

        public static bool IsDBNull(object obj)
        {
            return Convert.IsDBNull(obj);
        }

        public void Open()
        {
            if ((this.connection.State == ConnectionState.Broken) || (this.connection.State == ConnectionState.Closed))
            {
                this.connection.Open();
            }
        }

        public SqlDataReader RunProcedure(string selectsql)
        {
            this.Open();
            SqlCommand command = new SqlCommand(selectsql, this.connection);
            command.CommandType = CommandType.Text;
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public DataTable RunProcedure(string SqlString, string TableName)
        {
            DataTable dataTable = new DataTable();
            dataTable.TableName = TableName;
            this.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand(SqlString, this.connection);
            command.CommandType = CommandType.Text;
            adapter.SelectCommand = command;
            adapter.Fill(dataTable);
            return dataTable;
        }

        public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            this.Open();
            SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader();
        }

        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int affectedRows)
        {
            this.Open();
            SqlCommand command = this.BuildIntCommand(storedProcName, parameters);
            affectedRows = command.ExecuteNonQuery();
            return (int) command.Parameters["ReturnValue"].Value;
        }

        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            DataSet dataSet = new DataSet();
            this.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.BuildQueryCommand(storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
            return dataSet;
        }

        public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
        {
            this.connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.BuildIntCommand(storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
        }

        public object RunSqlScalar(string selectsql)
        {
            this.Open();
            SqlCommand command = new SqlCommand(selectsql, this.connection);
            return command.ExecuteScalar();
        }

        public SqlConnection Connection
        {
            get
            {
                return this.connection;
            }
        }

        public string ConnectionString
        {
            get
            {
                return this.connectionString;
            }
        }
    }
}

